package com.wframe.project.jnd.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;
import com.frame.database.JdataBean;
import com.frame.database.PageResult;
import com.wframe.framework.database.BaseDao;

@Repository
public class InformationDao extends BaseDao{

	public  List<JdataBean> getIssueByCode(String code){
		String sql="select * from wb_issue i where code=?";
		sql+=" order by orderid is null asc,orderid,id";
		return querySql(sql,new Object[]{code});
	}
	//帮助中心列表
	public PageResult queryEntrances(JdataBean parms,int size,int pageno){
		String sql = "SELECT wi.id,wi.title,wi.issuetime,wi.createtime,wi.state,wi.type,wi.ORDERID,wi.`code` AS wicode,wc.id AS wcid,wc.`code` AS wccode,wc.`name` AS wcname from wb_issue wi join wb_channel wc on wi.code=wc.code WHERE wc.id in(16,17,18,19,21,22,29) AND state=1 and (INVALIDTIME is null or INVALIDTIME>now()) and (ISSUETIME is null or ISSUETIME<=now())";
		List patmlist = new ArrayList();
		if(parms!=null){
			if(StringUtils.isNotBlank(parms.getString("like"))){
				sql+=" and title like ?";
				patmlist.add("%"+parms.getString("like")+"%");
			}
		}
		sql+="ORDER BY wi.ISSUETIME DESC";
		return querySqlMap2(sql,patmlist,size,pageno);
	}
	//帮助中心详情
	public JdataBean getInfoContentNew (String id) {
		return getObject("SELECT wi.id,wi.title,wi.issuetime,wi.createtime,wi.state,wi.type,wi.ORDERID,wi.`code` AS wicode,wc.id AS wcid,wc.`code` AS wccode,wc.`name` AS wcname,ic.CONTENT FROM wb_issue wi JOIN wb_channel wc ON wi. CODE = wc. CODE JOIN wb_issuecontent ic ON wi.id = ic.ISSUEID WHERE wc.id IN (16, 17, 18, 19, 21, 22, 29)AND state = 1 AND (INVALIDTIME IS NULL OR INVALIDTIME > now())AND (ISSUETIME IS NULL OR ISSUETIME <= now()) AND wi.id=? ",new Object[]{id});
	}
	//帮助中心分类
	public PageResult getcontentList(JdataBean pamrs,int pagesize,int pageno){
		String sql = "SELECT wis.id AS wisid, wc.code AS wccode,wc.name AS wcname,wc.id AS wcid,wis.title,wis.issuetime,wis.createtime,wis.state,wis.type,wis.orderid,wis.code as wiscode from wb_channel wc,wb_issue wis,wb_issuecontent wcon where wc.code=wis.code and wis.id = wcon.ISSUEID ";
		List patmlist = new ArrayList();
		if (pamrs != null) {
			if (StringUtils.isNotBlank(pamrs.getString("code"))) {
				sql+=" and wis.code =?";
				patmlist.add(pamrs.getString("code"));
			}
		}
		sql += " ORDER BY wis.issuetime DESC";

		return querySqlMap2(sql,patmlist,pagesize,pageno);
	}
	public PageResult queryInfos(JdataBean parms,int size,int pageno){
		String sql="select * from wb_issue i where state=1 and (INVALIDTIME is null or INVALIDTIME>now()) and (ISSUETIME is null or ISSUETIME<=now())";
		List patmlist=new ArrayList();
		if(parms!=null){
			if(StringUtils.isNotBlank(parms.getString("code"))){
				sql+=" and code like ?";
				patmlist.add(parms.getString("code")+"%");
			}
			if(StringUtils.isNotBlank(parms.getString("title"))){
				sql+=" and title like ?";
				patmlist.add("%"+parms.getString("title")+"%");
			}
		}
		sql+=" order by orderid is null asc,orderid desc,id desc";
		return querySqlMap2(sql, patmlist,size,pageno);
	}
	public PageResult wxqueryInfos(JdataBean parms,int size,int pageno){
		String sql="select * from wb_issue i where state=1 and (INVALIDTIME is null or INVALIDTIME>now()) and (ISSUETIME is null or ISSUETIME<=now())";
		List patmlist=new ArrayList();
		if(parms!=null){
			if(StringUtils.isNotBlank(parms.getString("code"))){
				sql+=" and code = ? ";
				patmlist.add(parms.getString("code"));
			}
		}

		sql+=" order by orderid is null asc,orderid ASC,id desc";
		return querySqlMap2(sql, patmlist,size,pageno);
	}




	public JdataBean getInfo(String id){
		return getObject("select * from wb_issue i where id=?", new Object[]{id});
	}
	public String getInfoContent(String id){
		JdataBean bean=getObject("select * from wb_issuecontent i where issueid=?", new Object[]{id});
		if(bean!=null)return bean.getDefault("content","").toString();
		else return "";
	}
	
	public JdataBean getChannel(String code){
		return getObject("select * from wb_channel where code=?", new Object[]{code});
	}
	
	public List<JdataBean> queryChannel(JdataBean parms){
		String sql="select * from wb_channel i where 1=1";
		List patmlist=new ArrayList();
		if(parms!=null){
			if(StringUtils.isNotBlank(parms.getString("code"))){
				sql+=" and code like ? and code <> ?";
				patmlist.add(parms.getString("code")+"%");
				patmlist.add(parms.getString("code"));
			}
			if(StringUtils.isNotBlank(parms.getString("name"))){
				sql+=" and name like ?";
				patmlist.add("%"+parms.getString("name")+"%");
			}
		}
		sql+=" order by orderno,id";
		return querySql2(sql, patmlist);
	}
	public List<Map> querySubChannel(String code){
		String sql="select * from wb_channel i where code like ? order by orderno desc,id";
		return querySqlMap(sql, new Object[]{code+"___"});
	}
	
	public PageResult queryStationnews(String userid,String grade,int size,int pageno){
		String sql="select n.id,n.title,n.content,n.grade,DATE_FORMAT(n.create_time,'%Y-%m-%d %H:%i') create_time,(case when r.newsid is null then 0 else 1 end) isread from wb_stationnews n left join wb_stationnews_read r on r.newsid=n.id and r.uids like ? where 1=1";
		List patmlist=new ArrayList();
		patmlist.add("%"+userid+",%");
		sql+=" and (n.grade =? or n.grade is null)";
		patmlist.add(grade);
		sql+=" order by id desc";
		return querySqlMap2(sql, patmlist,size,pageno);
	}

	public JdataBean queryStationnewsTotal(String userid, String grade) {
		String sql = "select count(*) total from wb_stationnews n left join wb_stationnews_read r on r.newsid=n.id and r.uids like ? where 1=1 and (n.grade =? or n.grade is null)";
		return getObject(sql,new Object[]{userid, grade});
	}
	public JdataBean getStationnews(String id){
		String sql = "select id,title,content,grade,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i') create_time from wb_stationnews where id=?";
		return getObject(sql,new Object[]{id});
	}
	public void setStationnewsRead(String userid){
		List<JdataBean> list=querySql("select newsid from wb_stationnews_read where uids is null or uids not like ?", new Object[]{"%"+userid+",%"});
		String ids="";
		int size=0;
		for(JdataBean d:list){
			size++;
			if(size<300){
				if(!ids.equals(""))ids+=",";
				ids+=d.getString("newsid");
			}else break;
		}
		if(StringUtils.isNotBlank(ids))
		execute("update wb_stationnews_read set uids=CONCAT(ifnull(uids,''),'"+userid+"',',') where newsid in ("+ids+")", null);
	}
	public int statNonews(String userid,String grade) throws Exception{
		JdataBean data=getObject("select count(newsid) cc from wb_stationnews_read r,wb_stationnews n where r.newsid=n.id and (n.grade =? or n.grade is null) and (r.uids is null or r.uids not like ?)", new Object[]{grade,"%"+userid+",%"});
		if(data!=null)return data.getInt("cc");
		else return 0;
	}

	public List<JdataBean> getIssueCode(String code) {
//			String sql="select * from (SELECT * FROM wb_issue WHERE code = ? ORDER BY issuetime desc) t GROUP BY t.title ";
//		String sql="select * from (SELECT * FROM wb_issue WHERE code = ? AND(INVALIDTIME>NOW())  ORDER BY issuetime desc) t GROUP BY t.title ";
		String sql="SELECT * FROM(SELECT * FROM wb_issue WHERE CODE = ? AND issuetime <= NOW() AND INVALIDTIME >= NOW() AND state = 1 ORDER BY INVALIDTIME ASC,issuetime DESC ) t GROUP BY t.title";

		return querySql(sql,new Object[]{code});
		}

}
